﻿Imports Microsoft.VisualBasic
Imports Tom0014
Imports Tom0014TableAdapters
Imports System.Web.UI.WebControls


Public Class utileria
    Private catalogo As New catalogo_generalDataTable
    Private adapCatalogo As New catalogo_generalTableAdapter
    Private ponderaciones As New ponderacionesTableAdapter

    ''' <summary>
    ''' SubRutina de llenado catalogo
    ''' </summary>
    ''' <remarks></remarks>
    Public Sub fillcatalogo()
        Me.catalogo = Me.adapCatalogo.GetCatalogo()
    End Sub

    ''' <summary>
    ''' Llena los DropDownList utilizados en la patallas desde la tabla
    ''' animal.catalogos
    ''' </summary>
    ''' <param name="myDropDown">DropDownList pasado por valor</param>
    ''' <param name="Cadena">Indica la clave de la categoria</param>
    ''' <returns>Devuelve DropDownList</returns>
    ''' <remarks>Utiliza esta funcion para llenar las opciones del formulario</remarks>
    Function fillCatalogo(ByVal myDropDown As DropDownList, ByVal cadena As String) As DropDownList
        myDropDown.DataSource = Me.catalogo.Select("cattip='" + cadena + "'", "catnom")
        myDropDown.DataBind()
        Return myDropDown
    End Function

    Function fillPondera(ByVal DropDown As DropDownList, ByVal Cadena As String) As DropDownList
        Dim categoriaTable As New ponderacionesDataTable
        categoriaTable = ponderaciones.GetPonderacionByCategoria(Cadena)
        DropDown.DataSource = categoriaTable
        DropDown.DataTextField = categoriaTable.Columns("etiqueta").ToString
        DropDown.DataValueField = categoriaTable.Columns("valor_interno").ToString
        DropDown.DataBind()
        Return DropDown
    End Function

    ''' <summary>
    ''' Funcion que devuelve la cadena WHERE para una consulta SQL
    ''' a traves de un filtro
    ''' </summary>
    ''' <param name="IdFiltro">Indica el numero de filtro a aplicar</param>
    ''' <returns>Cadena sWhere</returns>
    ''' <remarks></remarks>
    Public Function sWhereFiltro(ByVal IdFiltro As Integer) As String
        Dim sWhere, sWhereTientaAc, sWhereTientaAp, sWhereSanidad, sWhereLidiaCaballo, sWhereLidiaPie As String ' Cadena WHERE devuelva por la funcion
        Dim filtroDetalle As New filtroDetalleDataTable ' DataTable de los detalles del filtro
        Dim _filtroDetalle As New filtroDetalleTableAdapter
        Dim columna As New COLUMNSDataTable
        Dim _columna As New COLUMNSTableAdapter
        sWhere = ""
        sWhereTientaAc = ""
        sWhereTientaAp = ""
        sWhereSanidad = ""
        sWhereLidiaCaballo = ""
        sWhereLidiaPie = ""

        filtroDetalle = _filtroDetalle.GetFiltroDetalleById(IdFiltro)
        'Por cada registro habra que concatenar a la cadena where
        For Each row In filtroDetalle
            ' Verifica si existe el campo en la tabla
            Dim row_Campo As String
            If row.campo.StartsWith("_") Then
                row_Campo = row.campo.Remove(0, 1)
            ElseIf row.campo.StartsWith("/") Then
                row_Campo = row.campo.Remove(0, 1)
            Else
                row_Campo = row.campo
            End If


            columna = _columna.GetEsquemaByTablayCampo(row.tabla, row_Campo)
            If columna.Count = 1 Then ' Existe el campo en la tabla

                ' Concatenar la preposicion AND a los wheres
                Select Case row.tabla.ToUpper
                    Case "ANIMAL"
                        If Not (sWhere = "") Then
                            sWhere += " AND "
                        End If
                    Case "TIENTACABALLO"
                        If Not (sWhereTientaAc = "") Then
                            sWhereTientaAc += " AND "
                        End If
                    Case "TIENTAPIE"
                        If Not (sWhereTientaAp = "") Then
                            sWhereTientaAp += " AND "
                        End If
                    Case "LIDIACABALLO"
                        If Not (sWhereLidiaCaballo = "") Then
                            sWhereLidiaCaballo += " AND "
                        End If
                    Case "LIDIAPIE"
                        If Not (sWhereLidiaPie = "") Then
                            sWhereLidiaPie += " AND "
                        End If
                    Case "SANIDAD"
                        If Not (sWhereSanidad = "") Then
                            sWhereSanidad += " AND "
                        End If
                End Select

                'Obten el tipo de dato y valores
                Dim tipoCampo As String = columna(0).DATA_TYPE
                Dim tabla As String = row.tabla
                Dim campo As String = row.campo
                Dim valor1 As String = row.valor1
                Dim valor2 As String = row.valor2
                Dim sResul As String = ""

                If campo.StartsWith("_") Then

                    sResul = row_Campo + " " + valor1
                ElseIf campo.StartsWith("/") Then
                    sResul = row_Campo & " LIKE '%" + valor1.Replace("'", "''") + "%'"

                Else
                    ' Determinar si esta ente rangos los valores
                    If valor1 <> "" And valor2 <> "" Then
                        sResul = campo + " BETWEEN " + ToSQL(tipoCampo, valor1) + " AND " + ToSQL(tipoCampo, valor2)
                    Else
                        sResul = campo + " = " + ToSQL(tipoCampo, valor1)
                    End If

                End If



                

                ' concatenando valores al where
                Select Case tabla.ToUpper
                    Case "ANIMAL"
                        sWhere += sResul
                    Case "TIENTACABALLO"
                        sWhereTientaAc += sResul
                    Case "TIENTAPIE"
                        sWhereTientaAp += sResul
                    Case "LIDIACABALLO"
                        sWhereLidiaCaballo += sResul
                    Case "LIDIAPIE"
                        sWhereLidiaPie += sResul
                    Case "SANIDAD"
                        sWhereSanidad += sResul
                End Select

            End If
            columna = Nothing
        Next

        If Not (sWhereTientaAc = "") Then
            If Not (sWhere = "") Then sWhere += " AND "
            sWhereTientaAc = " animalid IN (SELECT animalid from TientaCaballo WHERE " + sWhereTientaAc + ")"
            sWhere += sWhereTientaAc

        End If

        If Not (sWhereTientaAp = "") Then
            If Not (sWhere = "") Then sWhere += " AND "
            sWhereTientaAp = " animalid IN (SELECT animalid from TientaPie WHERE " + sWhereTientaAp + ")"
            sWhere += sWhereTientaAp
        End If

        If Not (sWhereLidiaCaballo = "") Then
            If Not (sWhere = "") Then sWhere += " AND "
            sWhereLidiaCaballo = " animalid IN (SELECT animalid from LidiaCaballo WHERE " + sWhereLidiaCaballo + ")"
            sWhere += sWhereLidiaCaballo
        End If
        If Not (sWhereLidiaPie = "") Then
            If Not (sWhere = "") Then sWhere += " AND "
            sWhereLidiaPie = " animalid IN (SELECT animalid from LidiaPie WHERE " + sWhereLidiaPie + ")"
            sWhere += sWhereLidiaPie
        End If

        If Not (sWhereSanidad = "") Then
            If Not (sWhere = "") Then sWhere += " AND "
            sWhereSanidad = " animalid IN (SELECT animalid from Sanidad WHERE " + sWhereSanidad + ")"
            sWhere += sWhereSanidad
        End If



        ' Si existieron registro anexo la clausula WHERE 
        If Not (sWhere = "") Then
            sWhere = "WHERE (" + sWhere + ")"
        End If
        Return sWhere
    End Function
    ''' <summary>
    ''' Funcion que convierte la cadena al formato SQL solicitado por el DB
    ''' </summary>
    ''' <param name="tipo">Indica el tipo de datos a convertir</param>
    ''' <param name="valor">Indica el valor de la variable</param>
    ''' <returns>Regresa una cadena formateada</returns>
    ''' <remarks></remarks>
    Function ToSQL(ByVal tipo As String, ByVal valor As String) As String
        Dim sToSQL As String = ""

        Select Case tipo
            Case "varchar", "char", "nchar", "datetime", "text"
                sToSQL = "'" + valor.Replace("'", "''") + "'"
            Case "int", "numeric", "bit", "money", "float"
                sToSQL = valor
        End Select

        Return sToSQL
    End Function



End Class
